package com.wuliu.dao.impl;

import com.wuliu.dao.NewsDAO;
import com.wuliu.model.News;
import com.wuliu.model.NewsClass;
import com.wuliu.util.Jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class  NewsDAOImpl implements NewsDAO {
    @Override
    public List<News> findAllAndClass() {
        List<News> newsList=new ArrayList<>();
        try {
            Jdbc jdbc=new Jdbc();
            Connection connection=jdbc.connection();
            String sql="SELECT * FROM `news`,`news_class`  where news.cid=news_class.id";
            PreparedStatement statement=connection.prepareStatement(sql);
            ResultSet set=statement.executeQuery();
            while (set.next()){
                int id=set.getInt("id");
                String time=set.getString("time");
                String name=set.getString("name");
                String desc=set.getString("desc");
                String source=set.getString("source");
                String author=set.getString("author");
                int views=set.getInt("views");
                int cid=set.getInt("cid");
                int newsClassId=set.getInt("news_class.id");
                String newsClassName=set.getString("news_class.name");
                String newsClassImg =set.getString("news_class.img");
                NewsClass newsClass =new NewsClass(newsClassId,newsClassName,newsClassImg);

                News news=new News(id,time,name,desc,source,author,views,cid,newsClass);
                newsList.add(news);
            }
            set.close();
            statement.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return newsList;
    }

    @Override
    public News findByIdAndClass(int id) {

        News news=null;
        try {
            Jdbc jdbc=new Jdbc();
            Connection connection=jdbc.connection();
            String sql="select * from `news`,`news_class` where news.id=? and news.cid=news_class.id";
            PreparedStatement statement=connection.prepareStatement(sql);
            statement.setInt(1,id);
            ResultSet set=statement.executeQuery();
            while (set.next()){
                String time=set.getString("time");
                String name=set.getString("name");
                String desc=set.getString("desc");
                String source=set.getString("source");
                String author=set.getString("author");
                int views=set.getInt("views");
                int cid=set.getInt("cid");
                int newsClassId=set.getInt("news_class.id");
                String newsClassName=set.getString("news_class.name");
                String newsClassImg =set.getString("news_class.img");
                NewsClass newsClass =new NewsClass(newsClassId,newsClassName,newsClassImg);
                 news=new News(id,time,name,desc,source,author,views,cid,newsClass);
            }
            set.close();
            statement.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return news;
    }

    @Override
    public void add(News news) {
        try {
            Jdbc jdbc=new Jdbc();
            Connection connection=jdbc.connection();
            String sql="insert into news values(null,?,?,?,?,?,?,?)";
            PreparedStatement statement=connection.prepareStatement(sql);
            statement.setString(1,news.getTime());
            statement.setString(2,news.getName());
            statement.setString(3,news.getDesc());
            statement.setString(4,news.getSource());
            statement.setString(5,news.getAuthor());
            statement.setInt(6,news.getViews());
            statement.setInt(7,news.getCid());
            statement.execute();
            statement.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    @Override
    public void deleteById(int id) {
        try {
            Jdbc jdbc=new Jdbc();
            Connection connection=jdbc.connection();
            String sql="delete from news where id=?";
            PreparedStatement statement=connection.prepareStatement(sql);
            statement.setInt(1,id);
            statement.execute();
            statement.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }


    }

    @Override
    public void update(News news) {
        try {
//            System.out.println(news);
            Jdbc jdbc=new Jdbc();
            Connection connection=jdbc.connection();
            String sql="update news set time=?,name=?,`desc`=?,source=?,author=?,views=?,cid=? where id=?";
            PreparedStatement statement=connection.prepareStatement(sql);
            statement.setString(1,news.getTime());
            statement.setString(2,news.getName());
            statement.setString(3,news.getDesc());
            statement.setString(4, news.getSource());
            statement.setString(5,news.getAuthor());
            statement.setInt(6,news.getViews());
            statement.setInt(7,news.getCid());
            statement.setInt(8,news.getId());
            statement.execute();
            statement.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }



    @Override
    public News findById(int id) {
        News news =null;
        try {
            Jdbc jdbc=new Jdbc();
            Connection connection=jdbc.connection();
            String sql = "select * from news where id= ?";
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setInt(1, id);
            ResultSet set = statement.executeQuery();
            while (set.next()) {

                String time=set.getString("time");
                String name=set.getString("name");
                String desc=set.getString("desc");
                String source=set.getString("source");
                String author=set.getString("author");
                int views=set.getInt("views");
                int cid=set.getInt("cid");
                news  =new News(id,time,name,desc,source,author,views,cid);

            }
            set.close();
            statement.close();
            connection.close();


        } catch (Exception e) {
            e.printStackTrace();
        }

        return  news;
    }

    @Override
    public List<News> findAll() {
        List<News> newsList=new ArrayList<>();
        try {
            Jdbc jdbc=new Jdbc();
            Connection connection=jdbc.connection();
            String sql="select * from news";
            PreparedStatement statement=connection.prepareStatement(sql);
            ResultSet set=statement.executeQuery();
            while (set.next()){
                int id=set.getInt("id");
                String time=set.getString("time");
                String name=set.getString("name");
                String desc=set.getString("desc");
                String source=set.getString("source");
                String author=set.getString("author");
                int views=set.getInt("views");
                int cid=set.getInt("cid");
                News news=new News(id,time,name,desc,source,author,views,cid);
                newsList.add(news);
            }
            set.close();
            statement.close();
            connection.close();
        }catch (Exception e){
            e.printStackTrace();
        }
        return newsList;
    }
    @Override
    public List<News> findByCid(int cid) {
        List<News> newsList=new ArrayList<>();
        try {
            Jdbc jdbc=new Jdbc();
            Connection connection=jdbc.connection();
            String sql="select * from news where cid=?";
            PreparedStatement statement=connection.prepareStatement(sql);
            statement.setInt(1,cid);
            ResultSet set=statement.executeQuery();
            while (set.next()){
                int id=set.getInt("id");
                String time=set.getString("time");
                String name=set.getString("name");
                String desc=set.getString("desc");
                String source=set.getString("source");
                String author=set.getString("author");
                int views=set.getInt("views");
                News news=new News(id,time,name,desc,source,author,views,cid);
                newsList.add(news);
            }
            set.close();
            statement.close();
            connection.close();
        }catch (Exception e){
            e.printStackTrace();
        }
        return newsList;
    }
}
